﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using SaludOcupacional.Exceptions;
using SaludOcupacional.Model;
using System.Collections.ObjectModel;

namespace SaludOcupacional.DataAccess
{
    public class OftalmologicoDataAccess : IDisposable
    {
        private DatabaseManager dbManager;

        public void Dispose()
        {
            dbManager.Dispose();
        }
        public OftalmologicoDataAccess()
        {
            dbManager = new DatabaseManager();
        }

        public Oftalmologico BuscarAnalisisPorCi(int ci)
        {
            if (ci == 0)
            {
                throw new ArgumentNullException("ci");
            }
            // Create a Sql sentence
            string sqlsentence = "SELECT * FROM [AnalisisOftalmologico] WHERE ci_empleado = @Ci";

            dbManager.Command.CommandText = sqlsentence;
            dbManager.Command.Parameters.AddWithValue("@Ci", ci);

            Oftalmologico resp = null;

            try
            {
                DataSet dataSet = dbManager.Query();

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRow reader = dataSet.Tables[0].Rows[0];

                    resp = new Oftalmologico
                    {
                        CodAnalisis = Convert.ToInt32(reader["cod_analisis"], System.Globalization.CultureInfo.InvariantCulture),
                        CiEmpleado = Convert.ToInt32(reader["ci_empleado"], System.Globalization.CultureInfo.InvariantCulture),
                        UsoLentes = reader["uso_lentes"].ToString(),
                        OiSin = reader["oi_sin"].ToString(),
                        OdSin = reader["od_sin"].ToString(),
                        OiCon = reader["io_con"].ToString(),
                        OdCon = reader["od_con"].ToString(),
                        Miopia = reader["miopia"].ToString(),
                        Astigmatismo = reader["astigmatismo"].ToString(),
                        Hipermetropia = reader["hipermetropia"].ToString(),
                        Pterigiun = reader["pterigiun"].ToString(),
                        Pinguecula = reader["pinguecula"].ToString(),
                        Seudofaquia = reader["seudofaquia"].ToString(),
                        Daltonismo = reader["daltonismo"].ToString(),
                        Normal = reader["normal"].ToString(),
                        Accion = reader["acciones_atomar"].ToString(),
                        
                        Observaciones = reader["observaciones"].ToString(),
                        Fecha = reader["fecha"].ToString()

                    };
                }
            }
            catch (Exception)
            {
                throw;
            }

            return resp;
        }

        public void RegistrarAnalisis(Oftalmologico oftalmologico)
        {
            if (oftalmologico == null)
            {
                throw new LoginException("Invalid Input Parameters");
            }

            string sqlSentence = "INSERT INTO [dbo].[AnalisisOftalmologico]([ci_empleado],[uso_lentes],[oi_sin],[od_sin],[io_con],[od_con],[miopia],[astigmatismo],[hipermetropia],[pterigiun],[pinguecula],[seudofaquia],[daltonismo],[normal],[acciones_atomar],[observaciones],[fecha])VALUES ( ";
            sqlSentence += "@Ci, @lentes, @oiSin, @odSin, @oiCon, @odCon, @Miopia, @Astig, @Hiperm, @Pteri, @Pingue, @Seudof, @Dalton, @Normal, @Accion, @Observ, @fec )";

            dbManager.Command.CommandText = sqlSentence;
            dbManager.Command.CommandType = System.Data.CommandType.Text;



            dbManager.Command.Parameters.AddWithValue("@Ci", oftalmologico.CiEmpleado);
            dbManager.Command.Parameters.AddWithValue("@lentes", oftalmologico.UsoLentes);
            dbManager.Command.Parameters.AddWithValue("@oiSin", oftalmologico.OiSin);
            dbManager.Command.Parameters.AddWithValue("@odSin", oftalmologico.OdSin);
            dbManager.Command.Parameters.AddWithValue("@oiCon", oftalmologico.OiCon);
            dbManager.Command.Parameters.AddWithValue("@odCon", oftalmologico.OdCon);
            dbManager.Command.Parameters.AddWithValue("@Miopia", oftalmologico.Miopia);
            dbManager.Command.Parameters.AddWithValue("@Astig", oftalmologico.Astigmatismo);
            dbManager.Command.Parameters.AddWithValue("@Hiperm", oftalmologico.Hipermetropia);
            dbManager.Command.Parameters.AddWithValue("@Pteri", oftalmologico.Pterigiun);
            dbManager.Command.Parameters.AddWithValue("@Pingue", oftalmologico.Pinguecula);
            dbManager.Command.Parameters.AddWithValue("@Seudof", oftalmologico.Seudofaquia);
            dbManager.Command.Parameters.AddWithValue("@Dalton", oftalmologico.Daltonismo);
            dbManager.Command.Parameters.AddWithValue("@Normal", oftalmologico.Normal);
            dbManager.Command.Parameters.AddWithValue("@Accion", oftalmologico.Accion);
            dbManager.Command.Parameters.AddWithValue("@Observ", oftalmologico.Observaciones);
            dbManager.Command.Parameters.AddWithValue("@fec", oftalmologico.Fecha);


            try
            {
                int value = dbManager.NonQuery();

                if (value < 1)
                {
                    throw new LoginException("The record wasn't registered");
                }
            }
            catch (Exception e)
            {
                throw new LoginException("Error creating a user" + e.Message, e);
            }
        }

        public void EditarAnalisis(Oftalmologico oftalmologico)
        {
            if (oftalmologico == null)
            {
                throw new LoginException("The Analisis can´t be null");
            }

            string sqlSentence =
                "UPDATE [AnalisisOftalmologico]  SET [uso_lentes] = @lentes, [oi_sin] = @oiSin, [od_sin] = @odSin, [io_con] = @oiCon, ";
            sqlSentence = sqlSentence + "[od_con] = @odCon, [miopia] = @Miopia, ";
            sqlSentence = sqlSentence + "[astigmatismo] = @Astig, [hipermetropia] = @Hiperm, ";
            sqlSentence = sqlSentence + "[pterigiun] = @Pteri, [pinguecula] = @Pingue, ";
            sqlSentence = sqlSentence + "[seudofaquia] = @Seudof, [daltonismo] = @Dalton, ";
            sqlSentence = sqlSentence + "[normal] = @Normal, [acciones_atomar] = @Accion, ";
            sqlSentence = sqlSentence + "[observaciones] = @Observ, [fecha] = @fec ";
            sqlSentence = sqlSentence + " WHERE [ci_empleado] = @Ci";

            dbManager.Command.CommandText = sqlSentence;
            dbManager.Command.CommandType = System.Data.CommandType.Text;


            
            dbManager.Command.Parameters.AddWithValue("@lentes", oftalmologico.UsoLentes);
            dbManager.Command.Parameters.AddWithValue("@oiSin", oftalmologico.OiSin);
            dbManager.Command.Parameters.AddWithValue("@odSin", oftalmologico.OdSin);
            dbManager.Command.Parameters.AddWithValue("@oiCon", oftalmologico.OiCon);
            dbManager.Command.Parameters.AddWithValue("@odCon", oftalmologico.OdCon);
            dbManager.Command.Parameters.AddWithValue("@Miopia", oftalmologico.Miopia);
            dbManager.Command.Parameters.AddWithValue("@Astig", oftalmologico.Astigmatismo);
            dbManager.Command.Parameters.AddWithValue("@Hiperm", oftalmologico.Hipermetropia);
            dbManager.Command.Parameters.AddWithValue("@Pteri", oftalmologico.Pterigiun);
            dbManager.Command.Parameters.AddWithValue("@Pingue", oftalmologico.Pinguecula);
            dbManager.Command.Parameters.AddWithValue("@Seudof", oftalmologico.Seudofaquia);
            dbManager.Command.Parameters.AddWithValue("@Dalton", oftalmologico.Daltonismo);
            dbManager.Command.Parameters.AddWithValue("@Normal", oftalmologico.Normal);
            dbManager.Command.Parameters.AddWithValue("@Accion", oftalmologico.Accion);
            dbManager.Command.Parameters.AddWithValue("@Observ", oftalmologico.Observaciones);
            dbManager.Command.Parameters.AddWithValue("@fec", oftalmologico.Fecha);
            dbManager.Command.Parameters.AddWithValue("@Ci", oftalmologico.CiEmpleado);


            try
            {
                int value = dbManager.NonQuery();
                if (value < 1)
                {
                    throw new LoginException("The change wasn't registered");
                }

            }
            catch (Exception e)
            {
                throw new LoginException("Error edit profile", e);
            }
        }
    }
}
